import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Locale;
import java.util.Scanner;
import java.awt.Desktop;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;


import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import oracle.jdbc.pool.OracleDataSource;
import java.util.Date;

public class Commande {

	/**
	 * Cette méthode permet d'aaficher la liste de toutes les commandes d'un client
	 * @param client
	 */
	public static void afficherCommandes(Client client){		
		try
		{
		
			Statement stmt = Application.getStmt();
			
			// Passage de la transaction en read only 
			stmt.executeUpdate("SET TRANSACTION READ ONLY");
			//Requête pour les albums d'un client
			String req = "select idCommande,dateCommande,prixtotal from Commande where idclient like '"+client.getIdClient()+"'";
			ResultSet rset = stmt.executeQuery(req);
			StringBuffer res = new StringBuffer();
			String lineSep = System.getProperty("line.separator");
			res.append("idCommande                    Date                    PrixTotal");
			res.append(lineSep);
			while (rset.next())
			{
				res.append(rset.getString(1));
				res.append("                    ");
				res.append(rset.getString(2));
				res.append("                    ");
				res.append(rset.getString(3));
				res.append(lineSep);
			}
			System.out.println(res.toString());
			stmt.executeUpdate("COMMIT");

			rset.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void afficherCommande(int idCommande){
		
		Statement stmt = Application.getStmt();
		
		// Passage de la transaction en read only 
		try {
			stmt.executeUpdate("SET TRANSACTION READ ONLY");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//Requête pour les albums d'un client

		String req = "select a.idArticle,a.qte,f.libelle,f.prixFormat,al.titre from Article a,Format f,Album al where f.idFormat = a.idFormat AND a.idalbum = al.idalbum AND a.idCommande = "+idCommande;
		ResultSet rset = null;
		try {
			rset = stmt.executeQuery(req);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		StringBuffer res = new StringBuffer();
		String lineSep = System.getProperty("line.separator");
		res.append("idArticle                    Titre Album/Livre                    Format                    Prix Format                   Qte");
		res.append(lineSep);
		try {
			while (rset.next())
			{
				res.append(rset.getString(1));
				res.append("                    ");
				res.append(rset.getString(5));
				res.append("                    ");
				res.append(rset.getString(3));
				res.append("                    ");
				res.append(rset.getString(4));
				res.append("                    ");
				res.append(rset.getString(2));
				res.append(lineSep);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(res.toString());
		
		try {
			stmt.executeUpdate("COMMIT");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		try {
			rset.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static void saisieCommande(Client client){
		int choixAlbum, qteAlbum,choixFormat;
		boolean visible = false; 
		boolean finCommande = false;
		ArrayList<ParamCommandes> listeCommandes = new ArrayList<ParamCommandes>();
	
		System.out.println("Voici vos albums");
		Album.afficherTout(visible, client);
		ParamCommandes comClient = new ParamCommandes() ;
		try
		{
			ResultSet rset;
			String continueCommande = "";
			Scanner sc = new Scanner(System.in);
			Statement stmt = Application.getStmt();
			while( !finCommande ){
				System.out.println("Quel album voulez vous tirer : ");
				choixAlbum = sc.nextInt();//§§
			
				comClient.setChoixAlbum(choixAlbum);
				
				// Passage de la transaction en read only 
				stmt.executeUpdate("SET TRANSACTION READ ONLY");
				//Requête pour les albums d'un client
				String req = "select idalbum,titre,soustitre from Album where idclient like '"+client.getIdClient()+"' and idalbum ="+choixAlbum;
				rset = stmt.executeQuery(req);
	
				StringBuffer res = new StringBuffer();
				String lineSep = System.getProperty("line.separator");
				res.append("ID                    Titre                    SousTitre");
				res.append(lineSep);
	
				rset.next();
				res.append(rset.getString(1));
				res.append("                    ");
				String titreAlbumChoisi = rset.getString(2);//§§:
				res.append(rset.getString(2));
				res.append("                    ");
				String soustitreAlbumChoisi="                    ";//§§:
				if ( rset.getString(3) != null ){
					res.append(rset.getString(3));
					soustitreAlbumChoisi = rset.getString(3); 
				}
				res.append(lineSep);
				System.out.println("====================================================");
				System.out.println(res.toString());
				System.out.println("====================================================");
				stmt.executeUpdate("COMMIT");
				rset.close();
				
				comClient.setTitreAlbumChoisi(titreAlbumChoisi);
				comClient.setSoustitreAlbumChoisi(soustitreAlbumChoisi);
				
				/**
				 * Choix QUANTITE 
				 */
				 System.out.println("Quelle quantité ?");
				 qteAlbum = sc.nextInt();//§§
				
				 comClient.setQteAlbum(qteAlbum);
				 
				/**
				 * Choix du FORMAT 
				 */
				int nbRows = 0; 
				System.out.println("Choisisser l'un des formats suivants : ");
				req = "select idformat,libelle,prixformat from format";
				rset = stmt.executeQuery(req);
				res = new StringBuffer();
				res.append("ID                    Libellé                    Prix");
				res.append(lineSep);
				while (rset.next())
				{
					res.append(rset.getString(1));
					res.append("                    ");
					res.append(rset.getString(2));
					res.append("                    ");
					res.append(rset.getString(3));
					res.append(lineSep);
					nbRows++;
				}
				System.out.println("====================================================");
				System.out.println(res.toString());
				System.out.println("====================================================");
				stmt.executeUpdate("COMMIT");
				rset.close();
				choixFormat = sc.nextInt();//§§
				while( choixFormat > nbRows ){
					System.out.println("Ce numéro de format n'existe pas");
					choixFormat = sc.nextInt();				
				}
				req = "select libelle,prixFormat from FORMAT WHERE idFormat ="+choixFormat;
				rset = stmt.executeQuery(req);
				rset.next();
				String libelleFormat = rset.getString(1);//§§:
				String prixFormat = rset.getString(2);//§§
				stmt.executeUpdate("COMMIT");
				rset.close();
				
				comClient.setLibelleFormat(libelleFormat);
				comClient.setPrixFormat(prixFormat);
				comClient.setChoixFormat(choixFormat);
				
				listeCommandes.add(comClient);
				
				sc.nextLine();
				System.out.println("Voulez vous faire une autre commande");
				continueCommande = sc.nextLine();
				comClient  = new ParamCommandes();
				if ( !continueCommande.equals("o"))
					finCommande = true;
			}			
			
			/**
			 * REACPITULATIF
			 */
			
			System.out.println("==================================================================");
			System.out.println("IDALBUM                    TITRE                    SOUSTITRE                    QTE                    FORMAT                    PRIX-FORMAT");
			for( int i = 0; i < listeCommandes.size(); i++ )
				System.out.println(listeCommandes.get(i).getChoixAlbum()+"                    "+listeCommandes.get(i).getTitreAlbumChoisi()+"                    "+listeCommandes.get(i).getSoustitreAlbumChoisi()+"                    "+listeCommandes.get(i).getQteAlbum()+"                    "+listeCommandes.get(i).getLibelleFormat()+"                    "+listeCommandes.get(i).getPrixFormat());

			
			
			/**
			 * INSERTION DE LA COMMANDE DANS LA TABLE COMMANDE 
			 */

			 boolean adresseModifiee = false;
			 System.out.println("Voici votre adresse actuelle :"+client.getAdresse()+" "+client.getCodePostal()+" "+client.getVille());
			 System.out.println("Voulez vous changer l'adresse d'éxpedition (o/n): ");
			 String choixAdresse = sc.next();

			 String adresse="";
			 int codePostal= 0;
			 String ville="";
			 if ( choixAdresse.equals("o") ){
				 sc.nextLine();
				 System.out.println("Veuillez saisir l'adresse :");
				 adresse = sc.nextLine();
				 System.out.println("Veuillez saisir le code postal :");
				 codePostal = sc.nextInt();
				 sc.nextLine();
				 System.out.println("Veuillez saisir la ville :");
				 ville = sc.nextLine();
				 System.out.println("L'adresse d'expedition est "+adresse+" "+codePostal+" "+ville);
				 adresseModifiee = true;
			 }
		
			 String date1=new SimpleDateFormat("dd/MM/yyyy", Locale.FRANCE).format(new Date());
			 String heure=new SimpleDateFormat("HH:mm:ss", Locale.FRANCE).format(new Date());

			 stmt.executeUpdate("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
			 // Insertion dans COMMANDE
			 double prixChaqueCommande = 0;
			 for( int j=0; j < listeCommandes.size(); j++ ){
				 prixChaqueCommande += Double.valueOf(listeCommandes.get(j).getPrixFormat()) * listeCommandes.get(j).getQteAlbum();
			 }

			 String req = "INSERT INTO COMMANDE VALUES('',to_timestamp('"+date1+" "+heure+"','DD-MM-YYYY HH24:MI:SS'),"+prixChaqueCommande+",";
			 if( adresseModifiee )
				 req = req + "'"+adresse+"',"+codePostal+",'"+ville+"',";
			 else
				 req = req + "'"+client.getAdresse()+"',"+client.getCodePostal()+",'"+client.getVille()+"',";
			 req = req + "'"+client.getIdClient()+"')";
    		 int ret = stmt.executeUpdate(req);
			 // Fin de la transaction 
			 stmt.executeUpdate("COMMIT");

				
			 /**
			  * INSERTION DE LA COMMANDE DANS ARTICLE
			  */
				// Passage de la transaction en read only 
				stmt.executeUpdate("SET TRANSACTION READ ONLY");
			 	req = "select IDCOMMANDE from COMMANDE ORDER BY IDCOMMANDE DESC";
				rset = stmt.executeQuery(req);
				rset.next();
				int idCommande  = Integer.valueOf(rset.getString(1));
				rset.close();
				stmt.executeUpdate("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
				for( int i=0; i < listeCommandes.size(); i++ ){
					req = "INSERT INTO ARTICLE VALUES('',"+listeCommandes.get(i).getQteAlbum()+","+idCommande+","+listeCommandes.get(i).getChoixFormat()+","+listeCommandes.get(i).getChoixAlbum()+")" ;
					ret = stmt.executeUpdate(req);
				}
				 // Fin de la transaction 
				 stmt.executeUpdate("COMMIT");
				 String choix;
				 System.out.println("Voulez vous imprimer la facture(o/n) : ");
				 choix = sc.next();
				 if ( choix.equals("o") ){
					 try {
						obtenirFacture(idCommande,stmt);
					} catch (FileNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (DocumentException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				 }
				System.out.println("FIN");
			} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		
		
	}
	
	public static void obtenirFacture(int idCommande,Statement stmt) throws DocumentException, FileNotFoundException, SQLException {
		/**
		 * RECUPERATION DE VALEURS POUR LA FACTURE
		 */
		
		stmt.executeUpdate("SET TRANSACTION READ ONLY");
		//Requête pour les albums d'un client
		String req = "select cm.datecommande,cm.prixtotal,cm.adresseexp,cm.codepostalexp,cm.villeexp,cl.nom,cl.prenom from Commande cm,Client cl where cm.idclient = cl.mail AND cm.idcommande = "+idCommande;
		ResultSet rset = stmt.executeQuery(req);
		rset.next();
		String dateCommande = rset.getString(1);
		String prixTotal = rset.getString(2);
		String adresseExp = rset.getString(3);
		String codePostalExp = rset.getString(4);
		String villeExp = rset.getString(5);
		String nomClient = rset.getString(6);
		String prenomClient = rset.getString(7);
		stmt.executeUpdate("COMMIT");
		rset.close();
		
		/**
		 * Insertion des lignes dans la facture
		 */
	  	String FILE = "facture"+idCommande+".pdf";
	  	Font catFont = new Font(Font.FontFamily.TIMES_ROMAN, 18,
	      Font.BOLD);
	  	Font redFont = new Font(Font.FontFamily.TIMES_ROMAN, 12,
	      Font.NORMAL, BaseColor.RED);
	  	Font small= new Font(Font.FontFamily.TIMES_ROMAN, 12,
		      Font.NORMAL);	  
        Document document = new Document();
        PdfWriter.getInstance(document, new FileOutputStream(FILE));
        document.open();
 
        document.addTitle("FACURE");
        document.addSubject("Using iText");
        document.addKeywords("Java, PDF, iText");
	    Paragraph preface = new Paragraph();
	    // We add one empty line
	    addEmptyLine(preface, 1);
	    // Lets write a big header
	    preface.add(new Paragraph("FACTURE DE COMMANDE", catFont));
	
	    addEmptyLine(preface, 1);
	    // Will create: Report generated by: _name, _date
	    preface.add(new Paragraph("PhotoNum ",redFont));
	    addEmptyLine(preface, 3);
	    String date1=new SimpleDateFormat("dd/MM/yyyy", Locale.FRANCE).format(new Date());
	    String heure=new SimpleDateFormat("HH:mm:ss", Locale.FRANCE).format(new Date());
	    preface.add(new Paragraph("Le "+date1+" - "+heure+" à Valence",
	        small));
	
	    addEmptyLine(preface, 2);
	
	    preface.add(new Paragraph("Nom : "+nomClient,
	            small));
	    preface.add(new Paragraph("Prenom : "+prenomClient,
	            small));
	    preface.add(new Paragraph("Adresse : "+adresseExp,
	            small));
	    preface.add(new Paragraph("Code Postal : "+codePostalExp,
	            small));
	    preface.add(new Paragraph("Ville : "+villeExp,
	            small));
	    preface.add(new Paragraph("Date Commande : "+dateCommande,
	            small));	
	    addEmptyLine(preface, 2);
	
	    PdfPTable table = new PdfPTable(6);
	    table.setWidthPercentage((float) 110.3);
	    PdfPCell c1 = new PdfPCell(new Phrase("IdCmde"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	
	    c1 = new PdfPCell(new Phrase("IdArticle"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	    table.setHeaderRows(1);
	
	    
	    c1 = new PdfPCell(new Phrase("Titre Album/Livre"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	
	    c1 = new PdfPCell(new Phrase("Format Impression"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	    table.setHeaderRows(1);
	
	    c1 = new PdfPCell(new Phrase("Prix Format"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	    table.setHeaderRows(1);
	
	    c1 = new PdfPCell(new Phrase("Quantité"));
	    c1.setHorizontalAlignment(Element.ALIGN_CENTER);
	    table.addCell(c1);
	    table.setHeaderRows(1);

	    
		req = "select a.idArticle,a.qte,f.libelle,f.prixFormat,al.titre from Article a,Format f,Album al where f.idFormat = a.idFormat AND a.idalbum = al.idalbum AND a.idCommande = "+idCommande;
		rset = stmt.executeQuery(req);
		while( rset.next() ){
			String idArticle = rset.getString(1);
			String qte = rset.getString(2);
			String libelle = rset.getString(3);
			String prixFormat = rset.getString(4);
			String titre = rset.getString(5);
			table.addCell(String.valueOf(idCommande));
		    table.addCell(String.valueOf(idArticle));
		    table.addCell(titre);
		    table.addCell(libelle);
		    table.addCell(prixFormat);
		    table.addCell(qte);
		}
		stmt.executeUpdate("COMMIT");
		rset.close();

			
	
	    preface.add(table);
	
	    addEmptyLine(preface, 2);
	
	    PdfPTable table1 = new PdfPTable(2);
	    table1.setWidthPercentage((float) 110.3);
	    PdfPCell c2 = new PdfPCell(new Phrase("Prix Total"));
	    c2.setHorizontalAlignment(Element.ALIGN_LEFT);
	    c2.setBorderColor(BaseColor.RED);
	    table1.addCell(c2);
	
	    c2 = new PdfPCell(new Phrase(prixTotal));
	    c2.setHorizontalAlignment(Element.ALIGN_RIGHT);
	    c2.setBorderColor(BaseColor.RED);
	    table1.addCell(c2);

	
	    preface.add(table1);
	
	    // Now add all this to the document
	    document.add(preface);
	    
	    document.close();    
		Desktop desktop = null; 
		java.net.URI url; 
		try { 
			String chemin = "facture"+idCommande+".pdf";
			url = new java.net.URI(chemin); 
			if (Desktop.isDesktopSupported()) 
			{ 
				desktop = Desktop.getDesktop(); 
				desktop.browse(url); 
			} 
		} 
		catch (Exception ex) { 
		System.out.println(ex.getMessage()); 
		}
  }
	private static void addEmptyLine(Paragraph paragraph, int number) {
		    for (int i = 0; i < number; i++) {
		      paragraph.add(new Paragraph(" "));
		    }
		  }
}
